import sqlite3
import numpy as np
import os


# https://china.alliedvision.com/cn/%e4%ba%a7%e5%93%81/%e8%bd%af%e4%bb%b6.html

class Singleton(object):
    def __init__(self, cls):
        self._cls = cls
        self._instance = {}

    def __call__(self):
        if self._cls not in self._instance:
            self._instance[self._cls] = self._cls()
        return self._instance[self._cls]


@Singleton
class DatabaseInteraction:

    def __init__(self, database_address="action.db"):
        self.database_address = database_address
        self.conn = None
        self.cuis = None
        self.connect()
        self.id_to_class = {}
        self.init_id_to_class()

    def connect(self):
        self.conn = sqlite3.connect(self.database_address, check_same_thread=False)
        self.cuis = self.conn.cursor()

    def init_id_to_class(self):
        cursor = self.cuis.execute('select * from action_class')
        for row in cursor:
            id = row[0]
            class_name = row[1]
            self.id_to_class[id] = class_name

    def get_all_data(self):
        cursor = self.cuis.execute('select * from action order by id')
        id_list, class_list, address_list, dir_list = [], [], [], []
        for row in cursor:
            id_list.append(row[0])
            address_list.append(row[1])
            class_list.append(row[2])
            dir_list.append(row[3])
        return id_list, address_list, class_list, dir_list

    def get_id_to_class_dict(self):
        return self.id_to_class

    def get_all_class(self):
        cursor = self.cuis.execute('select * from action_class')
        self.class_list = []
        for row in cursor:
            self.class_list.append(row[1])
        return self.class_list

    def get_insert_id(self):
        # 返回下一个可插入的id值(type: str, format:str(index).zfill(5))

        cursor = self.cuis.execute('select * from action')
        id_list = []

        for i, row in enumerate(cursor):
            id_list.append(row[0])
        id_list = sorted(id_list)

        for index, id in enumerate(id_list):
            if int(id) != index:
                return str(index).zfill(5)

        return str(len(id_list)).zfill(5)

    def get_id_class_and_json_address(self):
        cursor = self.cuis.execute('select id, class, dir from action')
        id_list = []
        class_list = []
        json_address_list = []
        for row in cursor:
            id_list.append(row[0])
            class_list.append(row[1])
            json_address_list.append(row[2])
        return id_list, class_list, json_address_list

    def insert_one_data(self, id, address, class_, dir):
        self.cuis.execute(
            f"INSERT INTO ACTION(id, address, class, dir) VALUES ('{id}', '{address}', '{class_}', '{dir}')")
        self.conn.commit()

    def delete_one_data(self, id):
        # the format of id likes: '00001'
        command = f"DELETE FROM action WHERE id = '{id}'"
        self.cuis.execute(command)
        self.conn.commit()

    def update_selected_class(self, class_list):
        delete_all = f"DELETE FROM selected_class"
        self.cuis.execute(delete_all)
        for class_ in class_list:
            insert = f"INSERT INTO selected_class VALUES ('{class_}')"
            print(insert)
            self.cuis.execute(insert)
        self.conn.commit()

    def get_selected_class(self):
        cusor = self.cuis.execute('SELECT * FROM selected_class')
        selected_list = []
        for i in cusor:
            selected_list.append(i[0])
        return selected_list

    def __del__(self):
        self.conn.close()


if __name__ == '__main__':
    databaseInteraction = DatabaseInteraction()
    print(databaseInteraction.get_id_class_and_json_address())

